//
//  CET4DB.m
//  CET4Words
//
//  Created by lanou3g on 15/11/18.
//  Copyright © 2015年 yangyanyan. All rights reserved.
//

#import "CET4DB.h"
#import <sqlite3.h>
#import "CET4WordModel.h"

@implementation CET4DB

static CET4DB *cet4DB = nil;
+(instancetype)shareCET4DB {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        cet4DB = [[CET4DB alloc] init];
    });
    return cet4DB;
}



static sqlite3 *DB = nil;
-(void)openDB {
    NSString *sqlPath = [[NSBundle mainBundle] pathForResource:@"cet_four_word_6.db" ofType:nil];
    int result = sqlite3_open(sqlPath.UTF8String, &DB);
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功");
    } else {
        NSLog(@"打开数据库失败");
    }
}

//查询数据库表cet_four_word中的单词 音标，翻译
-(NSMutableArray *)selectCetFourWord {
    //打开数据库
    [self openDB];
    
    NSString *sql = @"SELECT english,symbol,chinese FROM cet_four_word";
    //创建命令指针
    sqlite3_stmt *stmt = nil;
    //预执行
    int prepareResult = sqlite3_prepare(DB, sql.UTF8String, -1, &stmt, NULL);
    NSMutableArray *cetArray = [NSMutableArray new];
    
    
    
    if (prepareResult == SQLITE_OK) {
        NSLog(@"预执行成功");
        //条件循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //初始化一个字典用来存放 查询的数据
            NSMutableDictionary *cetDic = [[NSMutableDictionary alloc] init];
            
            //sqlite3_column_blob(stmt, 1) 数据二进制内容
            //sqlite3_column_bytes(stmt, 1)] 数据长度
            
            NSData *englishData = [[NSData alloc] initWithBytes:sqlite3_column_blob(stmt, 0) length:sqlite3_column_bytes(stmt, 0)];
             NSData *symbolData = [[NSData alloc] initWithBytes:sqlite3_column_blob(stmt, 1) length:sqlite3_column_bytes(stmt, 1)];
             NSData *chineseData = [[NSData alloc] initWithBytes:sqlite3_column_blob(stmt, 2) length:sqlite3_column_bytes(stmt, 2)];

            NSString *englishString = [[NSString alloc] initWithData:englishData encoding:NSUTF8StringEncoding];
            NSString *symbolString = [[NSString alloc] initWithData:symbolData encoding:NSUTF8StringEncoding];
            NSString *chineseString = [[NSString alloc] initWithData:chineseData encoding:NSUTF8StringEncoding];
            
            [cetDic setValue:englishString forKey:@"word"];
            [cetDic setValue:symbolString forKey:@"symbol"];
            [cetDic setValue:chineseString forKey:@"chinese"];
            
            [cetArray addObject:cetDic];
        }
    }
    //释放
    sqlite3_finalize(stmt);
    return cetArray;
}


//查询数据库表cet4sentence中每个单词对应的句子
-(NSMutableArray *)selectCet4sentence {
    NSString *sql = @"SELECT sentence FROM cet4sentence";
    //创建命令指针
    sqlite3_stmt *stmt = nil;
    //预执行
    int prepareResult = sqlite3_prepare(DB, sql.UTF8String, -1, &stmt, NULL);
    NSMutableArray *cet4SentenceArray = [NSMutableArray new];
    if (prepareResult == SQLITE_OK) {
        NSLog(@"预执行成功");
        //条件循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //sqlite3_column_blob(stmt, 1) 数据二进制内容
            //sqlite3_column_bytes(stmt, 1)] 数据长度
            
           //将数据库中内容转化为data数据
            NSData *cet4SentenceData = [[NSData alloc] initWithBytes:sqlite3_column_blob(stmt, 0) length:sqlite3_column_bytes(stmt, 0)];
            
            //得到每个单词对应的三个例句及其翻译组成的数组，每个例句及其翻译都是一个字典
            NSArray *arr = [NSJSONSerialization JSONObjectWithData:cet4SentenceData options:NSJSONReadingMutableContainers error:nil];
            NSMutableString *str = [NSMutableString string];
            
            if (arr.count == 1) {
                [str appendString:@"<font color = \"white\">"];
                [str appendString:arr[0][@"chinese"]];
                [str appendString:@"</font>"];
                [str appendString:@"<br>"];
                [str appendString:arr[0][@"english"]];
            } else if (arr.count == 2) {
                [str appendString:arr[0][@"chinese"]];
                [str appendString:@"<br>"];
                [str appendString:arr[0][@"english"]];
                [str appendString:@"<br>"];
                [str appendString:arr[1][@"chinese"]];
                [str appendString:@"<br>"];
                [str appendString:arr[1][@"english"]];
            } else {
                [str appendString:@"<font color = \"white\">"];
                [str appendString:arr[0][@"chinese"]];
                [str appendString:@"<br>"];
                [str appendString:arr[0][@"english"]];
                [str appendString:@"<br>"];
                [str appendString:arr[1][@"chinese"]];
                [str appendString:@"<br>"];
                [str appendString:arr[1][@"english"]];
                [str appendString:@"<br>"];
                [str appendString:arr[2][@"chinese"]];
                [str appendString:@"<br>"];
                [str appendString:arr[2][@"english"]];
            }
           
            
            
            
//            NSLog(@"%@", str);
            [cet4SentenceArray addObject:str];
            
//            NSLog(@"%@", arr);
        }
    }
    //释放
    sqlite3_finalize(stmt);
    return cet4SentenceArray;
}






@end
